公共表表达式 (CTE) 教程

1. 概述

公共表表达式（Common Table Expressions，简称 CTE）是 SQL 中的一种临时结果集，可以在查询中多次引用。CTE 提供了一种更清晰、更易读的方式来编写复杂查询，尤其是在处理递归查询时。

1.1 什么是 CTE？

CTE 是一种临时命名结果集，它存在于单个 SQL 语句的执行范围内。CTE 可以被看作是一个临时视图，但它只在当前查询中有效。

1.2 CTE 的优点

• 可读性：CTE 使查询结构更清晰，便于理解和维护。
• 复用性：可以在同一查询中多次引用同一个 CTE。
• 递归查询：CTE 支持递归查询，这在处理层次结构数据时非常有用。

2. 基本语法

CTE 的基本语法如下：

WITH cte_name (column_list) AS (
    CTE_query_definition
)
SELECT * FROM cte_name;

• cte_name：CTE 的名称。
• column_list：可选，指定 CTE 返回的列名。
• CTE_query_definition：定义 CTE 的查询语句。

2.1 示例：简单的 CTE

假设我们有一个 employees 表，我们想要查询所有员工的姓名和部门名称。我们可以使用 CTE 来简化查询：

WITH emp_dept AS (
    SELECT e.employee_id, e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT * FROM emp_dept;

在这个例子中，emp_dept 是一个 CTE，它包含了员工的姓名和部门名称。然后我们可以在主查询中直接使用 emp_dept。

3. 递归 CTE

递归 CTE 是一种特殊的 CTE，它允许查询结果集包含自身的结果。递归 CTE 通常用于处理层次结构数据，如组织结构、分类树等。

3.1 递归 CTE 的语法

递归 CTE 的语法如下：

WITH RECURSIVE cte_name (column_list) AS (
    -- 初始查询（非递归部分）
    initial_query
    UNION ALL
    -- 递归查询
    recursive_query
)
SELECT * FROM cte_name;

• initial_query：定义初始结果集的查询。
• recursive_query：定义递归部分的查询，通常包含对 CTE 自身的引用。

3.2 示例：递归 CTE

假设我们有一个 employees 表，其中包含员工的 employee_id 和 manager_id，我们想要查询某个员工的所有下属（包括间接下属）：

WITH RECURSIVE subordinates AS (
    -- 初始查询：选择目标员工
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE employee_id = 1
    UNION ALL
    -- 递归查询：选择下属
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

在这个例子中，subordinates 是一个递归 CTE，它首先选择目标员工，然后递归地选择该员工的所有下属。

4. 实践练习

4.1 练习 1：简单的 CTE

假设我们有一个 sales 表，包含销售记录。我们想要查询每个销售员的销售总额。请使用 CTE 来实现这个查询。

WITH sales_summary AS (
    SELECT salesperson_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT * FROM sales_summary;

4.2 练习 2：递归 CTE

假设我们有一个 categories 表，包含产品的分类信息，其中 category_id 和 parent_category_id 表示分类的层次结构。我们想要查询某个分类及其所有子分类。

WITH RECURSIVE category_tree AS (
    -- 初始查询：选择目标分类
    SELECT category_id, category_name, parent_category_id
    FROM categories
    WHERE category_id = 1
    UNION ALL
    -- 递归查询：选择子分类
    SELECT c.category_id, c.category_name, c.parent_category_id
    FROM categories c
    JOIN category_tree ct ON c.parent_category_id = ct.category_id
)
SELECT * FROM category_tree;

5. 总结

公共表表达式（CTE）是 SQL 中非常有用的工具，它不仅提高了查询的可读性和复用性，还支持递归查询，适用于处理层次结构数据。通过本教程的学习，你应该已经掌握了 CTE 的基本语法和递归 CTE 的使用方法，并能够通过实践练习来巩固所学知识。

希望这篇教程对你理解 CTE 有所帮助，继续探索 SQL 的更多高级特性，提升你的数据库查询技能！